﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Globalization;

namespace nsDb
 {
/// <summary>
/// Provides the actual implementation of the methods interfacing to the abstract db layer 
/// </summary>
    public class ProcedureControl
        {

					private string _ConnectionString;
		public string ConnectionString
		{
			get { return _ConnectionString; }
			set { _ConnectionString = value; }
		} //eof property FieldName 


	public ProcedureControl ( string connectionStr )
		{
			this.ConnectionString = connectionStr; 
		} //eof constructor 

     

//callMe AddUniqueRow ( ref domainName, ref ds, ref msg,ref rds, ref tableName, ref  procedureName , ref itemId)
public bool AddUniqueRow ( ref string domainName, ref DataSet ds, ref string msg, ref string tableName
                                  , ref string procedureName , ref string itemId , ref string itemUniqueMetaId)
{

itemUniqueMetaId = "@" + itemUniqueMetaId;         
//debug Utils.Debugger.DebugDataSet ( "CHECK -- From AddUniqueRow START ref ds " , ref ds );
try
  {
  using (Database db = new Database( _ConnectionString))
  {
      DbCommand cmd = db.GetStoredProcedureCommand ( procedureName );

      int ret = 1; //assume false 
      db.AddInParam ( cmd , "@domain_user" , DbType.String , (object) domainName );
      db.AddInParam(cmd, "@ret", DbType.String, (object)ret);
      db.AddInOutParam(cmd, "@msg", DbType.String, (object)msg);

      foreach (DataTable dt in ds.Tables) //comm -- we have only one table
          {

          for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
              {
              DataRow dr = dt.Rows [ rowNum ];
                  object o;										//the object holding the value for this parameter
									string parameter;				//the param , which has to be declared for the stored procedure
                  DbType type        ;
              if ((bool)dr [ "include" ] == true)
                  {
                      parameter = "@" + System.Convert.ToString ( dr [ "COLUMN_NAME" ] );
                      o = dr [ "filledValue" ];


                  if (System.Convert.ToString ( dr [ "filledValue" ] ).Equals ( String.Empty ) ||
                      System.Convert.ToString ( dr [ "filledValue" ] ).Equals ( " " ) ||
                      System.Convert.ToString ( dr [ "filledValue" ] ).Equals ( "   " ))
                      {
                      type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb 
                          ( (string)dr [ "DATA_TYPE" ] );
                      o = DBNull.Value;
                      }
                  else
                      {
                      type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb 
                          ( (string)dr [ "DATA_TYPE" ] );
                      o = dr [ "filledValue" ];

                  } //eof else if filledValue is ""
        
                
                
                //debugOk Utils.Debugger.WriteIf ( "The value is " + o.ToString ( ) );
                //debugOk Utils.Debugger.WriteIf ( "The current row is " + rowNum );

									
                      //if this is PK add as OUT PARAM
                      if (Convert.ToBoolean ( dr ["IS_PRIMARY_KEY"]))
                      {
													Utils.Debugger.WriteIf ( "CHECK --- This is the PK" );
                          int id = Convert.ToInt32 ( itemId );
                          o = (object)id;
                          db.AddInOutParam ( cmd, parameter, DbType.Int32, 1, o );
                          Utils.Debugger.WriteIf("CHECK --- AddUniqueRow -- adding INOUT the following object " + o.ToString());
                      }
                      else
                      {
                        //check against SqlInjection
                        if (Utils.InputValidator.IsValidInput(ref msg, o.ToString()) != true)
                          return false;
                        db.AddInParam ( cmd, parameter, type, o );
                        Utils.Debugger.WriteIf("CHECK --- AddUniqueRow -- adding IN the following object " + o.ToString());
                      }
                  } //eof if ((bool)dr [ "include" ] == true)
              else
              { } //do nothing this row has not to be included
                  
              }	//eof foreach (DataTable dt in ds.Tables)
          }	//eof foreach (DataRow dr in dt.Rows)


      //debug Utils.Debugger.WriteIf ( "Executing the following procedure: " + procedureName );
      
      db.ExecuteNonQuery ( cmd);

      #region Loop2

      foreach (DataTable dt in ds.Tables) //comm -- we have only one table
      {

        for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
        {
          DataRow dr = dt.Rows[rowNum];
          
          if (Convert.ToString(dr["COLUMN_NAME"]).Trim().Equals("msg"))
          {
            msg = cmd.Parameters[Convert.ToString(dr["COLUMN_NAME"])].Value.ToString();
          }

          if (Convert.ToBoolean(dr["IS_PRIMARY_KEY"]))
          {
            Utils.Debugger.WriteIf("MARK --- AddUniqueRow --Loop2 --- IS_PK");
            itemId = cmd.Parameters["@" + (string)dr["COLUMN_NAME"]].Value.ToString();
          }

        }	//eof foreach (DataTable dt in ds.Tables)
      }	//eof foreach (DataRow dr in dt.Rows)
      #endregion Loop2

      ret = Convert.ToInt16 ( cmd.Parameters["@ret"].Value.ToString()) ;

      if (ret == 0)
        return true;
      else
        return false;

      
      
      
      //debug Utils.Debugger.DebugDataSet("CHECK DS END --- AddUniqueRow ", ref ds);
      Utils.Debugger.WriteIf ( "The new ProjectId to return is " + Convert.ToString (itemId ));
      return true ;
      } //eof using
  } //eof try

catch (System.FormatException e)
  {
    Utils.Debugger.WriteIf ( "AddUniqueRow -- The following Exception occured : \n" + e.Message );
    msg = "An error in the application occured.Report the following error  : \n" + e.Message ;
    return false;
  } //eof catch

catch (Exception e)
  {
  Utils.Debugger.WriteIf ( "AddUniqueRow -- The following Exception occured : \n" + e.Message );
  msg = "An error in the application occurred. Report the following error: " + e.Message;
  return false;
  } //eof catch



} //eof method AddUniqueRow( ref string domainName, ref DataSet ds, ref string msg, ref string tableName, ref string procedureName )


   //Meta_Data_Get ( ref msg, ref ds, ref dbObjectName , ref visible , ref tableName )
   public bool Meta_Data_Get ( ref string msg, ref DataSet ds, ref string dbObjectName , int visible , ref string tableName )
       {
       try
           {
           using (Database db = new Database( _ConnectionString))
           using (DbTransaction txn = db.BeginTransaction ( ))
               {   //comm -- so existing users are overwritten !!!
               //DEBUG Utils.Debugger.WriteIf ( "MetaDataBaseControl Meta_Data_Get START" );

               string storedProcedureName;
               DbCommand cmd;
               storedProcedureName = "Meta_Data_Get";

               cmd = db.GetStoredProcedureCommand ( storedProcedureName );
               
               db.AddInParam ( cmd, "@Name", DbType.String, dbObjectName );
               db.AddInParam ( cmd, "@Visible", DbType.Boolean, visible );
							 db.AddInParam ( cmd, "@Deftable", DbType.String, tableName );
              
               cmd.Transaction = txn;
               ds = db.ExecuteDataSet ( cmd );

               Utils.Data.DataSetGastArbeiter.ChopOffAts ( ref msg, ref ds );
               //Utils.Debugger.DebugDataSet ( "right after getting it chopping off all the characters after getting the ds from Meta_Data_Get", ref ds );

               //msg = "The request form details";
               return true;
               } //eof using
           } //eof try
       catch (Exception e)
           {
           Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
           msg = "An error in the application occurred. Report to advanced support error_code: mm1 " + e.Message;
           return false;
           } //eof catch

       } //eof public bool Meta_Data_Get ( ref string msg, ref DataSet ds, ref string tableName , int visible)


   public bool ListColumnValues ( ref string msg, ref DataSet des, 
			 ref string columnName, ref string tableName , ref string domainName , int formType)
   {

			 //if somebody tries sql Injection
			 if (Utils.InputValidator.IsValidInput ( ref msg , domainName ) != true)
					 return false;

    try
        {
        using (Database db = new Database( _ConnectionString))
        using (DbTransaction txn = db.BeginTransaction ( ))
            {
            DbCommand cmd = db.GetStoredProcedureCommand ( "List_Column_Values" );
						
            //debugOk Utils.Debugger.WriteIf ( "MetaDbControl.cs ListColumnValues + \nn" );

            db.AddInParam ( cmd, "@Table", DbType.String , (object)tableName);
            db.AddInParam ( cmd, "@Column", DbType.String, (object)columnName );
            db.AddInParam ( cmd, "@User", DbType.String, (object)domainName );
						db.AddInParam ( cmd , "@FormType" , DbType.String , (object) formType );

            cmd.Transaction = txn;
            des = db.ExecuteDataSet ( cmd );

            txn.Commit ( );
            return true;
            } //eof using
        } //eof try
    catch (Exception e)
        {
        Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
        msg = "An error in the application occurred. Report the following error code:mlcv6" + e.Message;
        return false;
        } //eof catch

       

    } //eof method ListColumnValues


   //GetItemByIdFromProcedure (   ref msg, ref rds, ref procedureName , ref itemId , ref itemMetaId)
        public bool GetItemByIdFromProcedure (   ref string msg, ref string domainName , ref DataSet rds, 
                                                ref string procedureName , ref string itemId , ref string itemMetaId)
            {
            //debugOk Utils.Debugger.WriteIf ( "MetaDbControl.cs GetItemByIdFromProcedure START" );

            try
                {
                using (Database db = new Database( _ConnectionString))
                using (DbTransaction txn = db.BeginTransaction ( ))
                    {
                    string parameter = "@" + itemMetaId;
                    object o = (object)itemId;
                    DbCommand cmd = db.GetStoredProcedureCommand ( procedureName );
                    //debugOk Utils.Debugger.WriteIf ( "From MetaDbControl.cs GetItemByIdFromProcedure - executing " + procedureName);

										db.AddInParam ( cmd , "@domain_user" , DbType.String ,  domainName );
										db.AddInParam ( cmd, parameter , DbType.String, o);
										
                    cmd.Transaction = txn;
                    rds = db.ExecuteDataSet ( cmd );

                    txn.Commit ( );
										//debugOk Utils.Debugger.DebugDataSet ( "after getting from GetItemByIdFromProcedure" , ref rds);
                    //NOW START THE RECORDING FOR THE LINK FUNTIONALITY
                    //create the new sqling
                    //Utils.Debugger.DebugDataSet ( "MetaDbControl.cs tracking		rds:", ref rds );
                    
                    return true;
                    } //eof using
            } //eof try
            
            catch (System.InvalidOperationException e) //comm -- occurs when no result set was found !!!
                {
                msg = "No items with the supplied criteria were found ! \n";
                Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
                return false;
                } //eof catch (System.InvalidOperationException)

            catch (System.IndexOutOfRangeException e) //comm -- occurs when no result set was found !!!
                {
                msg = "No items with the supplied criteria were found ! \n";
                Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
                return false;
                } //eof catch (System.IndexOutOfRangeException)

            catch (Exception e)
                {
                msg = "No items with the supplied criteria were found ! \n";
                Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
                return false ;  //comm -- returns false
                } //eof catch catch (Exception e)

            } //eof method GetItemByIdFromProcedure


				//RunProcGetDs( ref msg, ref ds)
        public bool RunProcGetDs( ref string msg, string domainName , string procedureName , ref DataSet ds )
            {
            try
                {
                using (Database db = new Database( _ConnectionString))
                {
                    DbCommand cmd = db.GetStoredProcedureCommand ( procedureName );
					          db.AddInParam ( cmd , "@domain_user" , DbType.String , (object)domainName );
                    
                    Utils.Debugger.WriteIf ( "MetaDbControl.cs RunProcGetDs + \n" );
                    

                    ds = db.ExecuteDataSet ( cmd );
                    //debug Utils.Debugger.DebugDataSet("from RunProcGetDs ", ref ds);
                    
                    //debug msg = "Select the values for your reports";
                    return true;
                    } //eof using
                } //eof try
            catch (Exception e)
                {
                Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
                msg = "An error in the application occurred. Report the following error code:mlcv2" + e.Message;
                return false;
                } //eof catch
            } //eof method RunProcGetDs



        //RunProcGetDsPassOneParam(ref msg, domainName, procedureName, ref ds , paramValue , paramMetaValue)
        public bool RunProcGetDsPassOneParam(ref string msg, string domainName, string procedureName, ref DataSet ds , string paramValue , string paramMetaValue)
        {

            //debug Utils.Debugger.WriteIf("START METHOD --- RunProcGetDsPassOneParam.cs ");
            paramMetaValue = "@" + paramMetaValue;
            try
            {
                using (Database db = new Database(_ConnectionString))
                {
                    DbCommand cmd = db.GetStoredProcedureCommand(procedureName);
                    db.AddInParam(cmd, "@domain_user", DbType.String, (object)domainName);
                    db.AddInParam(cmd, paramMetaValue, DbType.String, (object)paramValue);
                    


                    ds = db.ExecuteDataSet(cmd);
                    //debug Utils.Debugger.DebugDataSet("DEBUG DATASET FROM RunProcGetDs ", ref ds);

                    msg = "Select the values for your reports";
                    return true;
                } //eof using
            } //eof try
            catch (Exception e)
            {
                System.Diagnostics.StackTrace st = new System.Diagnostics.StackTrace();
                string mName = st.GetFrame(1).GetMethod().Name; 
                Utils.Debugger.WriteIf( mName + " --- The following Exception occured : \n" + e.Message);
                msg = "An error in the application occurred. Report the following error code:mlcv3" + e.Message;
                return false;
            } //eof catch
        } //eof public bool ListReports( ref string msg, ref DbDataReader dbdr, ref string columnName, ref string tableName )



//callMe public bool RunProcedurePassId ( ref msg , ref procedureName , ref itemMetaId , ref itemId)
public bool RunProcedurePassId ( ref string msg , string domainName , ref string procedureName , ref string itemMetaId , ref string itemId)
{
	//Utils.Debugger.WriteIf ( "START --- RunProcedurePassId " );
	try
	{
		itemMetaId = "@" + itemMetaId;
        //Utils.Debugger.WriteIf ( "msg  is " + msg );
        //Utils.Debugger.WriteIf ( "procedureName  is " + procedureName );
        //Utils.Debugger.WriteIf ( "itemMetaId is " + itemMetaId );
        //Utils.Debugger.WriteIf ( "itemId is " + itemId );

		using (Database db = new Database( _ConnectionString))
        {
            //how-to run procedure
            DbCommand cmd = db.GetStoredProcedureCommand ( procedureName );
            db.AddInParam(cmd, "@domain_user", DbType.String, (object)domainName);
            db.AddInParam ( cmd , itemMetaId , DbType.String , (object) itemId );

            db.ExecuteNonQuery ( cmd );
            msg = " Transaction executed successfully";
            return true;
		} //eof using
	} //eof try
	catch (Exception e)
	{
			Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
			msg = "An error in the application occurred. Report the following error code:mlcv4" + e.Message;
			return false;
	} //eof catch
} //eof public bool ListReports( ref string msg, ref DbDataReader dbdr, ref string columnName, ref string tableName )



				/// <summary>
				/// Updates any table by dynamically passing parameters 
				/// </summary>
				/// <param name="global.DomainName"></param>
				/// <param name="ds">parameter dataset</param>
				/// <param name="global.msg">message returned to user</param>
				/// <param name="rds">result dataset</param>
				/// <param name="connector">the type of connector in the query</param>
				/// <param name="tableName">the name of the table</param>
				/// <param name="procedureName">the name of the procedure called</param>
				/// <returns></returns>
				/// bool UpdateTable ( ref domainName, ref ds, ref msg, ref rds, ref tableName, ref procedureName )
				public bool UpdateTable ( ref string domainName , ref DataSet ds , ref string msg , ref string tableName , ref string procedureName )
				{
						string title = "";      //the title of the 
            int ret = 1;       //assume failed 
						try
						{
								using (Database db = new Database( _ConnectionString))
								{
										DbCommand cmd = db.GetStoredProcedureCommand ( procedureName );

										//debugOk Utils.Debugger.WriteIf ( "MetaDbControl.cs procedureName + " + procedureName + " executing \n" );
										//debugOk Utils.Debugger.DebugDataSet ( "From UpdateTable in MetaDataBaseControl " , ref ds );

										db.AddInParam ( cmd , "@domain_user" , DbType.String , (object) domainName );
                    db.AddInOutParam(cmd, "@msg", DbType.String, (object)msg);
                    db.AddInOutParam(cmd, "@ret", DbType.Int16, (object)ret);
                    #region Loop1
                    foreach (DataTable dt in ds.Tables) //comm -- we have only one table
										{
												int rowNum = 0;

												foreach (DataRow dr in dt.Rows)
												{
														if ((bool) dr [ "include" ] == true)
														{
																string parameter = "@" + dr [ "COLUMN_NAME" ];
																Utils.DescParser dp = new Utils.DescParser ( dr [ "DESCRIPTION" ] );
																title = dp.giveMe ( "title" );
																if (title == null || title.Equals ( String.Empty ))
																		title = System.Convert.ToString ( dr [ "COLUMN_NAME" ] );

																//Utils.Debugger.WriteIf ( "Adding in the following parameter " + parameter );
																DbType type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb 
																	( (string) dr [ "DATA_TYPE" ] );
																//debug Utils.Debugger.WriteIf ( "The current type IS " + type.ToString () );
																object o = dr [ "filledValue" ];

																//debug Utils.Debugger.WriteIf ( " my filled value is " + ( (string) ( dr [ "filledValue" ] ) ) );
																if (( (string) ( dr [ "filledValue" ] ) ).Equals ( String.Empty ) == true)
																{
																		type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb ( (string) dr [ "DATA_TYPE" ] );
																		//debug Utils.Debugger.WriteIf ( "BOOM BOOM BOOM - OBJECT IS SET TO NULL BEFORE INSERT" );
																		o = DBNull.Value;
																}
																else
																{
																		//check for SQL Injection 
																		if (Utils.InputValidator.IsValidInput ( ref msg , o.ToString () ) != true)
																				return false;

																		type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb ( (string) dr [ "DATA_TYPE" ] );
																		o = dr [ "filledValue" ];

																} //eof else if filledValue is ""

																//debug Utils.Debugger.WriteIf ( "The value is " + o.ToString () );
																//debug Utils.Debugger.WriteIf ( "The current row is " + rowNum );
																//debug Utils.Debugger.WriteIf ( "Adding in the following object " + o.ToString () );
																db.AddInParam ( cmd , parameter , type , o );
														} //eof if ((bool)dr [ "include" ] == true)
														else
                            { ; } //do nothing this row has not to be included
														rowNum++;
												}	//eof foreach (DataTable dt in ds.Tables)
										}	//eof foreach (DataRow dr in dt.Rows)

                    #endregion Loop1


                    //debug Utils.Debugger.WriteIf ( "Executing the following procedure: " + procedureName + " is : \n" + cmd.CommandText );
										//Utils.Debugger.DebugDataSet ( "From UpdateTable at the end " , ref ds );
										db.ExecuteNonQuery ( cmd );
                    msg = cmd.Parameters["@msg"].Value.ToString();
                    Utils.Debugger.WriteIf("CHECK --- msg from UpdateTable is : " + msg );
                    ret = Convert.ToInt16(cmd.Parameters["@ret"].Value.ToString());

                    if (ret == 0)
                      return true;
                    else
                      return false;


								} //eof using
						} //eof try

						catch (System.FormatException e)
						{
								Utils.Debugger.WriteIf ( "ERROR --- UpdateTable --- The following Exception occured : \n" + e.Message );
                msg = "An error in the application occurred. Report to the following message: \n" + e.Message;
								return false;
						} //eof catch

						catch (Exception e)
						{
              Utils.Debugger.WriteIf("ERROR --- UpdateTable --- The following Exception occured : \n" + e.Message);
								msg = "An error in the application occurred. Report the following error: " + e.Message;
								return false;
						} //eof catch
				} //eof method  public bool UpdateTable 


//RunProcedureWithParamsGetDs ( ref domainName, ref msg, ref dt, ref rds , ref procedureName )
public bool RunProcedureWithParamsGetDs ( ref string domainName, ref string msg, 
ref DataTable dt, ref DataSet rds , string procedureName )
{
    string title = "";      //the title of the 
    try
    {
	    using ( Database db = new Database( _ConnectionString) )

	    {
	    DbCommand cmd = db.GetStoredProcedureCommand ( procedureName );

	        //debugOk Utils.Debugger.WriteIf ( "MetaDbControl.cs procedureName + " + procedureName + " executing \n" );
	        Utils.Debugger.DebugDataTable ( "From RunProcedureWithParamsGetDs in MetaDataBaseControl " , ref dt );
	        //Utils.Debugger.WriteIf ( "CHECK --- RunProcedureWithParamsGetDs domainName is " + domainName ); 

        //variant	db.AddInParam ( cmd, "@domain_user", DbType.String, ( object )domainName );

		    int rowNum = 0;

		    foreach ( DataRow dr in dt.Rows )
		    {

                
		        string parameter = "@" + (string)dr ["COLUMN_NAME"];
            parameter = parameter.Replace("@@", "@");   

		        title = System.Convert.ToString ( dr ["COLUMN_NAME"] );

		        //Utils.Debugger.WriteIf ( "Adding in the following parameter " + parameter );
		        DbType type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb
			        ( ( string )dr ["DATA_TYPE"] );
		        //debug Utils.Debugger.WriteIf ( "The current type IS " + type.ToString ( ) );
		        object o = dr ["filledValue"];

		        //debug Utils.Debugger.WriteIf ( " my filled value is " + (( string )(dr ["filledValue"])) );
			
		        if ( (( string )(dr ["filledValue"])).Equals ( String.Empty ) == true )
		        {
			        type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb ( ( string )dr ["DATA_TYPE"] );
			        Utils.Debugger.WriteIf ( "BOOM BOOM BOOM - OBJECT IS SET TO NULL BEFORE INSERT" );
			        o = DBNull.Value;
		        }
		        else
		        {
			        //check for SQL Injection 
			        //if ( Utils.InputValidator.IsValidInput ( ref msg, o.ToString ( ) ) != true )
			        //  return false;

			        type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb ( ( string )dr ["DATA_TYPE"] );
			        o = dr ["filledValue"];

		        } //eof else if filledValue is ""

          //Utils.Debugger.WriteIf ( "The value is " + o.ToString ( ) );
          //Utils.Debugger.WriteIf ( "The current row is " + rowNum );
          //Utils.Debugger.WriteIf ( "Adding in the following object " + o.ToString ( ) );
			    db.AddInParam ( cmd, parameter, type, o );
			    rowNum++;

	    }	//eof foreach (DataRow dr in dt.Rows)




	    //debug Utils.Debugger.WriteIf ( "Executing the following procedure: " + procedureName + " is : \n" + cmd.CommandText );
		  

	    rds = db.ExecuteDataSet ( cmd );
	    msg = "Action completed successfully. ";
	    //Utils.Debugger.DebugDataSet ( "From RunProcedurePassParams at the end " , ref rds );
	    return true;
	    } //eof using
    } //eof try

    catch ( System.FormatException e )
    {
	    Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
	    msg = "An error in the application occured.Report the following error : \n" + e.Message;
	    return false;
    } //eof catch

    catch ( Exception e )
    {
	    Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
	    msg = "An error in the application occurred. Report the following error: " + e.Message;
	    return false;
    } //eof catch
} //eof method  public bool UpdateTable ( ref string domainName, ref DataSet ds, ref string msg,ref string tableName, ref string procedureName )


    


    //call RunSql(ref msg, domainName, ref procedureName ,ref sqlCmd)
    public bool RunSql(ref string msg, string domainName, ref string procedureName ,
        ref string sqlCmd , ref DataSet ds ) 
    {
        sqlCmd = Utils.Str.Str.FindAndReplaceCharInString(sqlCmd, "'", "''");
        
        Utils.Debugger.WriteIf("The sqlCmd is " + sqlCmd);
        Utils.Debugger.WriteIf("Run Sql from MetaControl START");
        try
        {
            using (Database db = new Database(_ConnectionString))
            using (DbTransaction txn = db.BeginTransaction())
            {
                //how-to run procedure
                DbCommand cmd = db.GetStoredProcedureCommand(procedureName);
                db.AddInParam(cmd, "@domain_user", DbType.String, (object)domainName);
                db.AddInOutParam(cmd, "@msg", DbType.String, (object)msg);
                db.AddInParam(cmd, "@sqlCmd", DbType.String, (object)sqlCmd);

                cmd.Transaction = txn;
                ds = db.ExecuteDataSet(cmd);
                return true;
            } //eof using
        } //eof try
        catch (Exception e)
        {
            //replaceHere 
			Utils.Debugger.WriteIf("The following Exception occured : \n" + e.Message);
            msg = "An error in the application occurred. Report the following error code:mlcv5" + e.Message;
            return false;
        } //eof catch
    } //eof method RunSql



    //callMe AddNewRowToTable (domainName, ref ds, ref msg, tableName , procedureName )
    public bool AddNewRowToTable (string domainName, ref DataSet ds, ref string msg, string tableName , string procedureName )
    {
        Utils.Debugger.WriteIf("START --- ProcedureControl --- AddNewRowToTable ");

        try
        {
            using (Database db = new Database(_ConnectionString))
            {
                DbCommand cmd = db.GetStoredProcedureCommand(procedureName);
                //todo add later history feature 
                //db.AddInParam(cmd, "@domain_user", DbType.String, (object)domainName);

                Utils.Debugger.DebugDataSet("CHECK -- AddNewRowToTable START", ref ds);
                
                #region Loop1
                foreach (DataTable dt in ds.Tables) //comm -- we have only one table
                {

                    for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
                    {
                        DataRow dr = dt.Rows[rowNum];
                        object o;										//the object holding the value for this parameter
                        string parameter;				//the param , which has to be declared for the stored procedure
                        DbType type;
                        db.AddInOutParam(cmd, "@msg", DbType.String, (object)msg);

                        if ((bool)dr["include"] == true)
                        {
                            parameter = "@" + System.Convert.ToString(dr["COLUMN_NAME"]);
                            o = dr["filledValue"];


                            if (System.Convert.ToString(dr["filledValue"]).Equals(String.Empty) ||
                                System.Convert.ToString(dr["filledValue"]).Equals(" ") ||
                                System.Convert.ToString(dr["filledValue"]).Equals("   "))
                            {
                                type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb
                                    ((string)dr["DATA_TYPE"]);
                                o = DBNull.Value;
                            }
                            else
                            {

                                type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb
                                    ((string)dr["DATA_TYPE"]);
                                o = dr["filledValue"];
                                //if somebody tries sql Injection
                                
                                if (Utils.InputValidator.IsValidInput(ref msg, o.ToString()) != true)
                                    return false;
                            } //eof else if filledValue is ""
                            //debugOk Utils.Debugger.WriteIf ( "The value is " + o.ToString ( ) );
                            //debugOk Utils.Debugger.WriteIf ( "The current row is " + rowNum );

                            

                            //if this is the second row add InOut Param
                            if (  Convert.ToBoolean(dr["IS_PRIMARY_KEY"]) )
                            {   //todo: enable if id is needed as out parameter
                                o = dr["filledValue"];
                                Utils.Debugger.WriteIf("CHECK --- Adding INOUT PK " + o.ToString());
                                int paramSize = System.Convert.ToInt16(dr["MAX_LENGTH"]);
                                db.AddInOutParam(cmd, parameter, type, paramSize, o);
                            }
                            else
                            {  
                                db.AddInParam(cmd, parameter, type, o);
                                Utils.Debugger.WriteIf("CHECK --- Adding in the following object " + o.ToString());
                            }
                        } //eof if ((bool)dr [ "include" ] == true)
                        else
                        { } //do nothing this row has not to be included

                    }	//eof foreach (DataTable dt in ds.Tables)
                }	//eof foreach (DataRow dr in dt.Rows)
                #endregion Loop1

                //debug Utils.Debugger.WriteIf("Executing the following procedure: " + procedureName);

                db.ExecuteNonQuery(cmd);

               //Add the newly created id from the stored procedure
                #region Loop2
                foreach (DataTable dt in ds.Tables) //comm -- we have only one table
                {

                    for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
                    {
                        DataRow dr = dt.Rows[rowNum];

                        if (Convert.ToString(dr["COLUMN_NAME"]).Trim().Equals("msg"))
                        {
                          msg = cmd.Parameters[Convert.ToString(dr["COLUMN_NAME"])].Value.ToString();
                        }


                        if (Convert.ToInt16(dr["IS_PRIMARY_KEY"]) == 1)
                        {
                            dr["filledValue"] = cmd.Parameters[Convert.ToString("@" + dr["COLUMN_NAME"])].Value.ToString();
                            Utils.Debugger.WriteIf("CHECK --- received from db out " + (string)dr["filledValue"]);
                        } //eof if this is primary 
                    else
                    { } //do nothing this row has not to be included

                    }	//eof foreach (DataTable dt in ds.Tables)
                }	//eof foreach (DataRow dr in dt.Rows)
                #endregion Loop2


                //Utils.Debugger.WriteIf ( "The new ProjectId to return is " + System.Convert.ToInt16 ( output ) );
                //msg = "The data update was successfull";
                Utils.Debugger.DebugDataSet("CHECK -- AddNewRowToTable END", ref ds);
                return true  ;
            } //eof using
        } //eof try

        catch (System.FormatException e)
        {
            Utils.Debugger.WriteIf("AddUniqueRow -- The following Exception occured : \n" + e.Message);
            msg = "An error in the application occured.Report the following error : \n" + e.Message;
            return false;
        } //eof catch

        catch (Exception e)
        {
            Utils.Debugger.WriteIf("AddUniqueRow -- The following Exception occured : \n" + e.Message);
            msg = "An error in the application occurred. Report the following error: " + e.Message;
            return false;
        } //eof catch



    } //eof method AddUniqueRow( ref string domainName, ref DataSet ds, ref string msg, ref string tableName, ref string procedureName )



    //callMe RunGenericProcGetDs ( ref domainName, ref ds, ref msg,ref rds, ref tableName, ref  procedureName , ref itemId)
    public bool RunGenericProcGetDs(ref string msg , string domainName, string procedureName , ref DataSet ds, ref DataSet rds )
    {
    Utils.Debugger.WriteIf ( "START METHOD -- RunGenericProcGetDs procedure: " + procedureName );
    //debug Utils.Debugger.DebugDataSet ( "-- From RunGenericProcGetDs START ref ds " , ref ds );
      try
      {
        using (Database db = new Database(_ConnectionString))
        {
          DbCommand cmd = db.GetStoredProcedureCommand(procedureName);

          int ret = 1; //assume false 
          //db.AddInParam(cmd, "@domain_user", DbType.String, (object)domainName);
          //db.AddInParam(cmd, "@ret", DbType.String, (object)ret);
          //db.AddInOutParam(cmd, "@msg", DbType.String, (object)msg);


          #region Loop1
          foreach (DataTable dt in ds.Tables) //comm -- we have only one table
          {

            for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
            {
              DataRow dr = dt.Rows[rowNum];
              object o;										//the object holding the value for this parameter
              string parameter;				//the param , which has to be declared for the stored procedure
              DbType type;

                parameter = "@" + System.Convert.ToString(dr["COLUMN_NAME"]);
          
                o = (object)dr["filledValue"];
                
                //SQL Injection check 
                if (Utils.InputValidator.IsValidInput(ref msg, o.ToString()) != true)
                  return false;


                if (System.Convert.ToString(dr["filledValue"]).Equals(String.Empty) ||
                    System.Convert.ToString(dr["filledValue"]).Equals(" ") ||
                    System.Convert.ToString(dr["filledValue"]).Equals("   "))
                {
                  type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb
                      ((string)dr["DATA_TYPE"]);
                  o = DBNull.Value;
                }
                else
                {
                  type = DbTypeConverter.ConvertFromDbTypeStrToDbTypeForDb
                      ((string)dr["DATA_TYPE"]);
                  o = dr["filledValue"];

                } //eof else if filledValue is ""



                //debugOk Utils.Debugger.WriteIf ( "The value is " + o.ToString ( ) );
                //debugOk Utils.Debugger.WriteIf ( "The current row is " + rowNum );


                //if this is PK add as OUT PARAM
                if (Convert.ToString(dr["PARAMETER_MODE"]).Equals("OUT"))
                {
                  Utils.Debugger.WriteIf("CHECK --- This is INOUT");
                  db.AddInOutParam(cmd, parameter, DbType.Int32, 1, o);
                  Utils.Debugger.WriteIf("CHECK --- RunGenericProcGetDs -- adding INOUT the following object " + o.ToString());
                }
                else
                {
                  //check against SqlInjection
                  db.AddInParam(cmd, parameter, type, o);
                  Utils.Debugger.WriteIf("CHECK --- RunGenericProcGetDs -- adding IN the following object " + o.ToString());
                }


            }	//eof foreach (DataTable dt in ds.Tables)
          }	//eof foreach (DataRow dr in dt.Rows)

          #endregion Loop1
          

        rds = db.ExecuteDataSet(cmd);

          #region Loop2

          foreach (DataTable dt in ds.Tables) //comm -- we have only one table
          {

            for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
            {
              DataRow dr = dt.Rows[rowNum];

              //TODO: ADD HERE MSG , RET , DOMAIN_USER
              //if (Convert.ToString(dr["COLUMN_NAME"]).Trim().Equals("msg"))
              //{
              //  msg = cmd.Parameters[Convert.ToString(dr["COLUMN_NAME"])].Value.ToString();
              //}
              
              if (Convert.ToString(dr["PARAMETER_MODE"]).Equals("OUT"))
              {
                Utils.Debugger.WriteIf("MARK --- RunGenericProcGetDs --Loop2 --- GET OUT PARAM");
                dr ["filledValue"] = cmd.Parameters["@" + (string)dr["COLUMN_NAME"]].Value.ToString();
              }

            }	//eof foreach (DataTable dt in ds.Tables)
          }	//eof foreach (DataRow dr in dt.Rows)
          #endregion Loop2


          Utils.Debugger.DebugDataSet("CHECK DS END --- RunGenericProcGetDs ", ref ds);
          //Utils.Debugger.WriteIf("The new ProjectId to return is " + Convert.ToString(itemId));


          return true;
          //TODO: use ret valeu
          //ret = Convert.ToInt16(cmd.Parameters["@ret"].Value.ToString());

          //if (ret == 0)
          //  return true;
          //else
          //  return false;
          
        } //eof using
      } //eof try

      catch (System.FormatException e)
      {
        Utils.Debugger.WriteIf("RunGenericProcGetDs -- The following Exception occured : \n" + e.Message);
        msg = "An error in the application occured.Report the following error  : \n" + e.Message;
        return false;
      } //eof catch

      catch (Exception e)
      {
        Utils.Debugger.WriteIf("RunGenericProcGetDs -- The following Exception occured : \n" + e.Message);
        msg = "An error in the application occurred. Report the following error: " + e.Message;
        return false;
      } //eof catch



    } //eof method 



    } //eof class ProcedureControl
} //eof nsDb


